/*
 * Copyright (C), 2002-2017, 苏宁易购电子商务有限公司
 * FileName: MainPushExcelHandle.java
 * Author:   15050536
 * Date:     2017年1月6日 下午2:14:10
 * Description: //模块目的、功能描述      
 * History: //修改记录
 * <author>      <time>      <version>    <desc>
 * 修改人姓名             修改时间            版本号                  描述
 */
package com.suning.sawp.service.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

/**
 * 主推 处理excel的类
 *
 * @author 15050536 石键平
 */
public class MainPushExcelHandle extends AbstractReaderFile {

	private static final Logger LOGGER = LoggerFactory.getLogger(MainPushExcelHandle.class);

	private Workbook workbook = null;

	private Sheet sheetBook = null;

	private Cell[] cells = null;

	private List<CellRangeAddress> mergeCells;

	public MainPushExcelHandle(File file) {
		this.workbook = createWorkbook(file);
	}

	public MainPushExcelHandle(MultipartFile file) {
		try {
			this.inputStream = file.getInputStream();
			this.workbook = createWorkbook(file.getOriginalFilename(), this.inputStream);
		} catch (IOException e) {
		    LOGGER.error("MainPushExcelHandle ERROR", e);
			throw new RuntimeException("读取文件异常:" + e.getMessage());
		}
	}

	/**
	 * 根据excel文件获取所有的sheet
	 *
	 * @param file
	 * @return
	 */
	public List<Sheet> getSheetsFromWorkbook() {
		List<Sheet> list = new ArrayList<Sheet>();
		for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
			list.add(workbook.getSheetAt(i));
		}
		return list;
	}

	/**
	 * 功能描述: 封装好的读取excel操作，返回List<List<String>>,外围List为行，里面List为列<br>
	 *
	 * @param sheetBook
	 *            待处理的工作表
	 * @param current
	 *            从第几行开始读取数据
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<List<String>> getExcelContentFromSheet(Sheet sheetBook, int current, int column) {
		try {
			this.current = current;
			this.rows = sheetBook.getLastRowNum() + 1;
			this.column = column;
			this.sheetBook = sheetBook;
			// 获取所有的合并单元格
			this.mergeCells = getAllMergeCell();

			List<String> everyStrList = null;
			List<List<String>> contextList = new ArrayList<List<String>>();
			while (this.hasNext()) {
				everyStrList = (List<String>) this.next();
				contextList.add(everyStrList);
			}

			return contextList;
		} catch (Exception e) {
		    LOGGER.error("getExcelContentFromSheet ERROR", e);
			throw new RuntimeException("读取文件异常:" + e.getMessage());
		}

	}

	public boolean hasNext() {
		if (this.current >= this.rows) {
			return false;
		}
		Row row = this.sheetBook.getRow(this.current++);
		if (null == row) {
			throw new RuntimeException("导入文档中存在空行,请使用正确的导入模板，总行数:" + this.rows + "，发生异常行数:" + this.current);
		}
		int lastCellNum = row.getLastCellNum();
		if (this.column != -1) {
			lastCellNum = this.column;
		}
		cells = new Cell[lastCellNum];

		for (int i = 0; i < cells.length; i++) {
			cells[i] = row.getCell(i);
		}

		return true;
	}

	public Object next() {
		return getStrings(this.cells);
	}

	public void close() throws IOException {
		super.close();

		if (this.workbook == null)
			return;

		this.workbook = null;
	}

	private List<String> getStrings(Cell[] cells) {
		if (cells == null) {
			return new ArrayList<String>(0);
		}

		List<String> result = new LinkedList<String>();

		for (int i = 0; i < cells.length; ++i) {
			if (cells[i] != null) {
				result.add(getStringCellValue(cells[i]));
			} else {
				result.add(null);
			}
		}
		return result;
	}

	/**
	 * 功能描述: <br>
	 * simple factory
	 *
	 * @see [相关类/方法](可选)
	 * @since [产品/模块版本](可选)
	 */
	private Workbook createWorkbook(File srcFile) {
		Workbook xwb = null;
		String fileName = srcFile.getName();
		try {
			InputStream is = new FileInputStream(srcFile);
			xwb = createWorkbook(fileName, is);
		} catch (FileNotFoundException e) {
			LOGGER.error("Read xlsx file error", e);
			throw new RuntimeException("读取文件失败:" + e.getMessage());
		}
		return xwb;
	}

	/**
	 * 功能描述: <br>
	 * simple factory
	 *
	 * @see [相关类/方法](可选)
	 * @since [产品/模块版本](可选)
	 */
	private Workbook createWorkbook(String suffix, InputStream is) {
		Workbook xwb = null;

		if (suffix.endsWith("xlsx")) {
			try {
				xwb = new XSSFWorkbook(is);
			} catch (Exception e) {
				LOGGER.error("Read xlsx file error", e);
				throw new RuntimeException("读取文件失败:" + e.getMessage());
			}
		} else if (suffix.endsWith("xls")) {
			try {
				xwb = new HSSFWorkbook(is);
			} catch (Exception e) {
				LOGGER.error("Read xlsx file error", e);
				throw new RuntimeException("读取文件失败:" + e.getMessage());
			}
		} else {
			throw new RuntimeException("只能识别xls或者xlsx结尾的excel文件");
		}

		return xwb;
	}

	/**
	 * be able to read as string value from a non-string type cell. Every cell
	 * value transform to string format.
	 *
	 * @param cell
	 * @return
	 */
	private String getStringCellValue(Cell cell) {
		if (cell == null) {
			return "";
		} else {
			Object ret = null;
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_FORMULA:// formula
				try {
					switch (cell.getCachedFormulaResultType()) {
					case Cell.CELL_TYPE_STRING:
						ret = cell.getStringCellValue();
						break;
					case Cell.CELL_TYPE_NUMERIC:
						ret = NumberUtils.left4Add5(cell.getNumericCellValue());
						break;
					default:
						ret = cell.getStringCellValue();
					}
				} catch (IllegalStateException e) {
					ret = StringUtils.EMPTY;
					String zb = cell.getRowIndex() + "_" + cell.getColumnIndex();
					LOGGER.warn("读取函数异常,坐标为：{}", zb, e);
				}
				break;
			case Cell.CELL_TYPE_NUMERIC:
				if (DateUtil.isCellDateFormatted(cell)) {
					ret = dateToString(cell.getDateCellValue(), "yyyy-MM-dd");
				} else {
					ret = NumberUtils.changeENotation(cell.getNumericCellValue());
				}
				break;
			case Cell.CELL_TYPE_STRING:
				ret = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_BLANK:// blank
				ret = getMergedRegionValue(cell.getRowIndex(), cell.getColumnIndex());
				break;
			case Cell.CELL_TYPE_ERROR:
				ret = "";
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				ret = new Boolean(cell.getBooleanCellValue());
				break;
			default:
				try {
					ret = cell.getStringCellValue();
				} catch (Exception e) {
					ret = "";
				}
			}

			return ret.toString().trim();
		}
	}

	private String dateToString(Date paramDate, String format) {
		SimpleDateFormat localSimpleDateFormat = new SimpleDateFormat(format);
		localSimpleDateFormat.setLenient(false);
		return localSimpleDateFormat.format(paramDate);
	}

	public List<CellRangeAddress> getAllMergeCell() {
		List<CellRangeAddress> mergeCells = new ArrayList<CellRangeAddress>();
		int sheetMergeCount = this.sheetBook.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = this.sheetBook.getMergedRegion(i);
			mergeCells.add(ca);
		}
		return mergeCells;
	}

	public String getMergedRegionValue(int row, int column) {
		for (CellRangeAddress ca : this.mergeCells) {
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();

			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					Cell cell = this.sheetBook.getRow(firstRow).getCell(firstColumn);
					if (cell.getCellType() != Cell.CELL_TYPE_BLANK) {
						return getStringCellValue(cell);
					}
					return StringUtils.EMPTY;
				}
			}
		}
		return "";
	}

}
